package Com.HupochuanAdmin.Dal.Tab;

import java.util.Hashtable;
import java.util.List;
import java.util.Vector;

import Com.Hupochuan.Dal.DBManager.DBManager;
import Com.Hupochuan.Vo.Actor;

public class ActorTab {
	/*
	 * 本类私有对象
	 */
	private DBManager<Actor> dbManager = new DBManager<Actor>(Actor.class);
	
	/*
	 * 单一实例
	 */
	private ActorTab(){}
	private static ActorTab actorTab = new ActorTab();
	public static ActorTab getObject(){
		return actorTab;
	}
	
	/*
	 * 添加数据
	 */
	public int appendData(String name){
		//参数部分
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" insert into Actor set ");
		sqlBuff.append(" 	name=${name}, ");
		sqlBuff.append(" 	createTime=now(), ");
		sqlBuff.append(" 	updateTime=now() ");
		
		//sql参数部分
		paraHash.put("name",name);
		
		//执行sql 并返回参数
		return dbManager.updateSql(sqlBuff.toString(),paraHash);
	}
	
	/*
	 * 根据ID 更新数据
	 */
	public int updateData(String logoPath,long id){
		//参数部分
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" update Actor set ");
		sqlBuff.append(" logoPath=${logoPath}, ");
		sqlBuff.append(" updateTime=now() ");
		sqlBuff.append(" where id=${id} ");
		
		//sql参数部分
		paraHash.put("logoPath",logoPath);
		paraHash.put("id",id);
		
		//执行sql 并返回参数
		return dbManager.updateSql(sqlBuff.toString(),paraHash);
	}
	
	/*
	 * 根据ID 更新数据
	 */
	public int updateData(String name,String baikeUrl,String xlwbUrl,String txwbUrl,String bdtbUrl,float weight,long id){
		//参数部分
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" update Actor set ");
		sqlBuff.append(" 	name=${name}, ");
		sqlBuff.append(" 	baikeUrl=${baikeUrl}, ");
		sqlBuff.append(" 	xlwbUrl=${xlwbUrl}, ");
		sqlBuff.append(" 	txwbUrl=${txwbUrl}, ");
		sqlBuff.append(" 	bdtbUrl=${bdtbUrl}, ");
		sqlBuff.append(" 	weight=${weight}, ");
		sqlBuff.append(" 	updateTime=now() ");
		sqlBuff.append(" where id=${id} and ( ");
		sqlBuff.append(" 	name!=${name} ");
		sqlBuff.append(" 	or baikeUrl!=${baikeUrl} ");
		sqlBuff.append(" 	or xlwbUrl!=${xlwbUrl} ");
		sqlBuff.append(" 	or txwbUrl!=${txwbUrl} ");
		sqlBuff.append(" 	or bdtbUrl!=${bdtbUrl} ");
		sqlBuff.append(" 	or weight!=${weight} ");
		sqlBuff.append(" ) ");
		
		//sql参数部分
		paraHash.put("name",name);
		paraHash.put("baikeUrl",baikeUrl);
		paraHash.put("xlwbUrl",xlwbUrl);
		paraHash.put("txwbUrl",txwbUrl);
		paraHash.put("bdtbUrl",bdtbUrl);
		paraHash.put("weight",weight);
		paraHash.put("id",id);
		
		//执行sql 并返回参数
		return dbManager.updateSql(sqlBuff.toString(),paraHash);
	}
	
	/*
	 * 根据ID 删除数据
	 */
	public int deleteData(long id){
		//参数部分
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" delete from Actor where id=${id} ");
		
		//sql参数部分
		paraHash.put("id",id);
		
		//执行sql 并返回参数
		return dbManager.updateSql(sqlBuff.toString(),paraHash);
	}
	
	/*
	 * 获得演员数量
	 */
	public long findActorCount(String logoPath){
		//参数部分
		long actorCount = 0;
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select count(id) as actorCount from Actor where logoPath=${logoPath} ");
		
		//sql参数部分
		paraHash.put("logoPath",logoPath);
		
		//执行sql
		actorCount = dbManager.querySqlReturnLong(sqlBuff.toString(), paraHash);
		
		//返回列表
		return actorCount;
	}
	
	
	/*
	 * 获得演员数量
	 */
	public long findActorCount(String searchType,String searchKey,String showRow){
		//参数部分
		long actorCount = 0;
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select count(id) as actorCount from Actor ");
		
		sqlBuff.append(" where ");
		
		//搜索电影名
		if(searchType.equals("movie")){
			sqlBuff.append(" id in ( ");
			sqlBuff.append(" 	select actorId from MovieActor ");
			sqlBuff.append(" 	left join Movie on MovieActor.movieId=Movie.id ");
			sqlBuff.append(" 	where Movie.name like ${searchKey} ");
			sqlBuff.append(" ) ");
		}
		//搜索角色名
		else if(searchType.equals("role")){
			sqlBuff.append(" id in ( ");
			sqlBuff.append(" 	select actorId from MovieActor where roleName like ${searchKey} ");
			sqlBuff.append(" ) ");
		}
		//搜素演员名
		else{
			sqlBuff.append(" name like ${searchKey} ");
		}

		//缺少
		if(showRow.equals("not")){
			sqlBuff.append(" and ( ");
			sqlBuff.append(" logoPath='' ");
			sqlBuff.append(" or baikeUrl='' ");
			sqlBuff.append(" or xlwbUrl='' ");
			sqlBuff.append(" or txwbUrl='' ");
			sqlBuff.append(" or bdtbUrl='' ");
			sqlBuff.append(" ) ");
		}
		//缺少头像
		else if(showRow.equals("notLogo")){
			sqlBuff.append(" and logoPath='' ");
		}
		//缺少百科
		else if(showRow.equals("notBaike")){
			sqlBuff.append(" and baikeUrl='' ");
		}
		//缺少新浪微博
		else if(showRow.equals("notXlwb")){
			sqlBuff.append(" and xlwbUrl='' ");
		}
		//缺少腾讯微博
		else if(showRow.equals("notTxwb")){
			sqlBuff.append(" and txwbUrl='' ");
		}
		//缺少百度贴吧
		else if(showRow.equals("notBdtb")){
			sqlBuff.append(" and bdtbUrl='' ");
		}
		
		//sql参数部分
		paraHash.put("searchKey","%"+searchKey+"%");
		
		//执行sql
		actorCount = dbManager.querySqlReturnLong(sqlBuff.toString(), paraHash);
		
		//返回列表
		return actorCount;
	}
	
	/*
	 * 获得演员列表
	 */
	public List<Actor> findActorList(String searchType,String searchKey,String showRow,String order,boolean asc,long pageNo,long pageSize){
		//参数部分
		List<Actor> actorList = new Vector<Actor>();
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select Actor.*,( ");
		sqlBuff.append(" 	select count(id) from MovieActor ");
		sqlBuff.append(" 	where actorId=Actor.id ");
		sqlBuff.append(" ) as movieCount ");
		sqlBuff.append(" from Actor ");
		
		sqlBuff.append(" where ");
		
		//搜索电影名
		if(searchType.equals("movie")){
			sqlBuff.append(" id in ( ");
			sqlBuff.append(" 	select actorId from MovieActor ");
			sqlBuff.append(" 	left join Movie on MovieActor.movieId=Movie.id ");
			sqlBuff.append(" 	where Movie.name like ${searchKey} ");
			sqlBuff.append(" ) ");
		}
		//搜索角色名
		else if(searchType.equals("role")){
			sqlBuff.append(" id in ( ");
			sqlBuff.append(" 	select actorId from MovieActor where roleName like ${searchKey} ");
			sqlBuff.append(" ) ");
		}
		//搜素演员名
		else{
			sqlBuff.append(" name like ${searchKey} ");
		}
		
		//缺少
		if(showRow.equals("not")){
			sqlBuff.append(" and ( ");
			sqlBuff.append(" logoPath='' ");
			sqlBuff.append(" or baikeUrl='' ");
			sqlBuff.append(" or xlwbUrl='' ");
			sqlBuff.append(" or txwbUrl='' ");
			sqlBuff.append(" or bdtbUrl='' ");
			sqlBuff.append(" ) ");
		}
		//缺少头像
		else if(showRow.equals("notLogo")){
			sqlBuff.append(" and logoPath='' ");
		}
		//缺少百科
		else if(showRow.equals("notBaike")){
			sqlBuff.append(" and baikeUrl='' ");
		}
		//缺少新浪微博
		else if(showRow.equals("notXlwb")){
			sqlBuff.append(" and xlwbUrl='' ");
		}
		//缺少腾讯微博
		else if(showRow.equals("notTxwb")){
			sqlBuff.append(" and txwbUrl='' ");
		}
		//缺少百度贴吧
		else if(showRow.equals("notBdtb")){
			sqlBuff.append(" and bdtbUrl='' ");
		}
		
		//排序
		sqlBuff.append(" order by ");
		//字段含有中文就转换下
		if(order.equals("name")){
			sqlBuff.append(" convert( ").append(order).append(" using gbk) ");
		}
		else{
			sqlBuff.append(order);
		}
		
		//顺序
		if(asc){
			sqlBuff.append(" asc ");
		}
		else{
			sqlBuff.append(" desc ");
		}
		sqlBuff.append(" ,id ");
		sqlBuff.append(" limit ${pageNo},${pageSize} ");
		
		//sql参数部分
		paraHash.put("searchKey","%"+searchKey+"%");
		paraHash.put("pageNo",(pageNo - 1) * pageSize);
		paraHash.put("pageSize",pageSize);
		
		//执行sql 
		actorList = this.dbManager.querySqlReturnVoList(sqlBuff.toString(), paraHash);
		
		//返回列表
		return actorList;
	}
	
	/*
	 * 获得演员ID
	 */
	public long findActorId(String name){
		//参数部分
		long actorId = 0;
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select id from Actor where name=${name} ");
		
		//sql参数部分
		paraHash.put("name",name);

		//执行sql
		actorId = dbManager.querySqlReturnLong(sqlBuff.toString(), paraHash);
		
		//返回
		return actorId;
	}
	
	/*
	 * 获得演员对象
	 */
	public Actor findActor(long id){
		//参数部分
		Actor actor = null;
		StringBuffer sqlBuff = new StringBuffer();
		Hashtable<String,Object> paraHash = new Hashtable<String,Object>();
		
		//sql语句部分
		sqlBuff.append(" select * from Actor where id=${id} ");
		
		//sql参数部分
		paraHash.put("id",id);

		//执行sql
		actor = dbManager.querySqlReturnVo(sqlBuff.toString(), paraHash);
		
		//返回
		return actor;
	}
}
